package com.juzi.wopu.service.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.juzi.wopu.domain.CartCheck;
import com.juzi.wopu.domain.CartCheckResponse;
import com.juzi.wopu.query.CartCheckQuery;
import com.juzi.wopu.service.ICartCheckService;

public class CartCheckServiceImpl extends BaseServiceImpl<CartCheck> implements ICartCheckService{

	@Override
	public Map<String, String> getTotalCount(CartCheckQuery query) {
		
		Map<String, String> map=new HashMap<>();
		
		String sql="select sum(iCount),sum(dPrice) FROM tCartCheck where 1=1 ";
		
		if(query.getBeginDate()!=null && query.getEndDate()!=null){
			SimpleDateFormat dateFormat=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			
			sql=sql+" and dtInsert > '"+dateFormat.format(query.getBeginDate())+
					"' and dtInsert < '"+dateFormat.format(query.getEndDate())+"'";
			
		}
		
		int totalNum=-1;
		double totalMoney=-1;
		ResultSet resultSet=BaseDao.executeQuerySql(sql);
		if(resultSet!=null){
			try {
				while(resultSet.next()){
					totalNum=resultSet.getInt(1);
					totalMoney=resultSet.getDouble(2);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		map.put("totalNum", totalNum+"");
		map.put("totalMoney", totalMoney+"");
		return map;
	}

	@Override
	public List<CartCheckResponse> queryAllCartCheck() {
		List<CartCheckResponse> list=new ArrayList<>();
		SimpleDateFormat dFormat=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		
//		String sql1="select cartCheck.iStatus,code3.sName as status, cartCheck.sCheckNo,cartCheck.iCount,"
//				+ "cartCheck.dPriceSum,cartCheck.sName,sysUser.iId as userId,"
//				+ "sysUser.sUserNo as userName, cartCheck.dtInsert,cartCheck.dtCheckSuc,"
//				+ "code1.iId as iType,code1.sName as type,code2.iId as iTypeSub,"
//				+ "code2.sName as typeSub from dbo.tCartCheck cartCheck,"
//				+ "dbo.tCartShelf cartShelf,dbo.tCanteenMenu canteenMenu,"
//				+ "dbo.tSysUser sysUser,dbo.tSysCode code1,dbo.tSysCode code2,dbo.tSysCode code3 "
//				+ "where cartCheck.iShelfId=cartShelf.iId and cartCheck.iStatus=code3.iId and "
//				+ "cartShelf.iGoodsId=canteenMenu.iId and cartCheck.iUserId=sysUser.iId and "
//				+ "cartShelf.iType=code1.iId and canteenMenu.iType=code2.iId and "
//				+ "cartShelf.iType=910 order by cartCheck.dtInsert desc";
		
		String sql1="select cartPlace.iCompany,code4.sName as companyName, "
				+ "cartCheck.iStatus,code3.sName as status, cartCheck.sCheckNo,"
				+ "cartCheck.iCount,cartCheck.dPriceSum,cartCheck.sName,"
				+ "sysUser.iId as userId,sysUser.sUserNo as userName, "
				+ "cartCheck.dtInsert,cartCheck.dtCheckSuc,code1.iId as iType,"
				+ "code1.sName as type,code2.iId as iTypeSub,code2.sName as typeSub "
				+ "from dbo.tCartCheck cartCheck,dbo.tCartShelf cartShelf,"
				+ "dbo.tCanteenMenu canteenMenu,dbo.tCartCheckCode cartCheckCode,"
				+ "dbo.tCartPlace cartPlace,dbo.tSysUser sysUser,dbo.tSysCode code1,"
				+ "dbo.tSysCode code2,dbo.tSysCode code3,dbo.tSysCode code4 "
				+ "where cartCheck.iShelfId=cartShelf.iId and "
				+ "cartCheck.iStatus=code3.iId and cartShelf.iGoodsId=canteenMenu.iId "
				+ "and cartCheck.iUserId=sysUser.iId and cartShelf.iType=code1.iId "
				+ "and canteenMenu.iType=code2.iId and cartShelf.iType=910 and "
				+ "cartCheckCode.iId=cartCheck.iCodeId and "
				+ "cartPlace.iPlaceId=cartCheckCode.iPlaceId and "
				+ "cartPlace.iCompany=code4.iId order by cartCheck.dtInsert desc";
		
		ResultSet resultSet=BaseDao.executeQuerySql(sql1);
		if(resultSet!=null){
			try {
				while(resultSet.next()){
					CartCheckResponse response=new CartCheckResponse();
					response.setsCheckNo(resultSet.getString("sCheckNo"));
					response.setiCount(resultSet.getLong("iCount"));
					response.setdPriceSum(resultSet.getDouble("dPriceSum"));
					response.setsName(resultSet.getString("sName"));
					response.setUserId(resultSet.getLong("userId"));
					response.setUserName(resultSet.getString("userName"));
					response.setDtInsert(resultSet.getTimestamp("dtInsert"));
					response.setDtCheckSuc(resultSet.getTimestamp("dtCheckSuc"));
					response.setiType(resultSet.getLong("iType"));
					response.setType(resultSet.getString("type"));
					response.setiTypeSub(resultSet.getLong("iTypeSub"));
					response.setTypeSub(resultSet.getString("typeSub"));
					response.setiStatus(resultSet.getLong("iStatus"));
					response.setStatus(resultSet.getString("status"));
					
					response.setiCompany(resultSet.getLong("iCompany"));
					response.setCompanyName(resultSet.getString("companyName"));
					list.add(response);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
//		String sql2="select cartCheck.iStatus,code3.sName as status,cartCheck.sCheckNo,cartCheck.iCount,cartCheck.dPriceSum,"
//				+ "cartCheck.sName,sysUser.iId as userId,sysUser.sUserNo as userName, "
//				+ "cartCheck.dtInsert,cartCheck.dtCheckSuc,code1.iId as iType,"
//				+ "code1.sName as type,code2.iId as iTypeSub,code2.sName as typeSub from "
//				+ "dbo.tCartCheck cartCheck,dbo.tCartShelf cartShelf,dbo.tProduct product,"
//				+ "dbo.tSysUser sysUser,dbo.tSysCode code1,dbo.tSysCode code2, dbo.tSysCode code3 "
//				+ "where cartCheck.iStatus=code3.iId and "
//				+ "cartCheck.iShelfId=cartShelf.iId and cartShelf.iGoodsId=product.iId "
//				+ "and cartCheck.iUserId=sysUser.iId and cartShelf.iType=code1.iId "
//				+ "and product.iTypeSub=code2.iId and cartShelf.iType!=910 "
//				+ "order by cartCheck.dtInsert desc";
		
		String sql2="select cartPlace.iCompany,code4.sName as companyName,"
				+ "cartCheck.iStatus,code3.sName as status,cartCheck.sCheckNo,"
				+ "cartCheck.iCount,cartCheck.dPriceSum,cartCheck.sName,"
				+ "sysUser.iId as userId,sysUser.sUserNo as userName, "
				+ "cartCheck.dtInsert,cartCheck.dtCheckSuc,code1.iId as iType,"
				+ "code1.sName as type,code2.iId as iTypeSub,code2.sName as typeSub "
				+ "from dbo.tCartCheck cartCheck,dbo.tCartShelf cartShelf,"
				+ "dbo.tProduct product,dbo.tCartCheckCode cartCheckCode,"
				+ "dbo.tCartPlace cartPlace,dbo.tSysUser sysUser,dbo.tSysCode code1,"
				+ "dbo.tSysCode code2, dbo.tSysCode code3, dbo.tSysCode code4 "
				+ "where cartCheck.iStatus=code3.iId and cartCheck.iShelfId=cartShelf.iId "
				+ "and cartShelf.iGoodsId=product.iId and cartCheck.iUserId=sysUser.iId "
				+ "and cartShelf.iType=code1.iId and product.iTypeSub=code2.iId "
				+ "and cartShelf.iType!=910 and cartCheckCode.iId=cartCheck.iCodeId "
				+ "and cartPlace.iPlaceId=cartCheckCode.iPlaceId and "
				+ "cartPlace.iCompany=code4.iId order by cartCheck.dtInsert desc";
		
		ResultSet resultSet1=BaseDao.executeQuerySql(sql2);
		if(resultSet1!=null){
			try {
				while(resultSet1.next()){
					CartCheckResponse response=new CartCheckResponse();
					response.setsCheckNo(resultSet1.getString("sCheckNo"));
					response.setiCount(resultSet1.getLong("iCount"));
					response.setdPriceSum(resultSet1.getDouble("dPriceSum"));
					response.setsName(resultSet1.getString("sName"));
					response.setUserId(resultSet1.getLong("userId"));
					response.setUserName(resultSet1.getString("userName"));
					response.setDtInsert(resultSet1.getTimestamp("dtInsert"));
					response.setDtCheckSuc(resultSet1.getTimestamp("dtCheckSuc"));
					response.setiType(resultSet1.getLong("iType"));
					response.setType(resultSet1.getString("type"));
					response.setiTypeSub(resultSet1.getLong("iTypeSub"));
					response.setTypeSub(resultSet1.getString("typeSub"));
					response.setiStatus(resultSet1.getLong("iStatus"));
					response.setStatus(resultSet1.getString("status"));
					
					response.setiCompany(resultSet1.getLong("iCompany"));
					response.setCompanyName(resultSet1.getString("companyName"));
					
					//排序
					Date date=response.getDtInsert();
					for(int i=0;i<list.size();i++){
						if(i==list.size()-1){
							list.add(response);
							break;
						}else if(date.compareTo(list.get(i).getDtInsert())<0 && 
								date.compareTo(list.get(i+1).getDtInsert())>=0){
							list.add(i+1, response);
							break;
						}
					}
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return list;
	}
}